from dbutils.pooled_db import PooledDB
import configparser
import pymysql
import time
class SqlHelper():
    def __init__(self):
        self.cofig()
        self.mysql_pool()
    def mysql_pool(self):
        while True:
            try:
                self.mysql_pool_list = PooledDB(creator=pymysql,
                                            maxcached=200,
                                            blocking=True,#默认False，即达到最大连接数时，再取新连接将会报错，True，达到最大连接数时，新连接阻塞，等待连接数减少再连接
                                            ping=4,
                                            host=self.MYSQL_HOST, port=self.MYSQL_PORT, user=self.MYSQL_USER,
                                            password=self.MYSQL_PASSWORD,
                                            db=self.MYSQL_DB,
                                            charset='utf8'
                                            )
            except BaseException as e:
                print(f'数据库链接错误{e}')
                self.mysql_pool_list = None
            if self.mysql_pool_list:
                print('数据库链接成功')
                break
            time.sleep(5)
    #获取一条数据库链接
    def get_conn(self):
        conn = self.mysql_pool_list.connection()
        cur = conn.cursor()
        return conn,cur
    #关闭数据库链接
    def close_conn(self,conn,cur):
        cur.close()
        conn.close()
    #charu数据库
    def insert_infor(self,sqlStr,params):
        conn,cur = self.get_conn()
        try:
            cur.execute(sqlStr,params)
            last_id  = cur.lastrowid
            conn.commit()
            return last_id
        except BaseException as e:
            print('数据库写入错误',e)
            return 0
        finally:
            self.close_conn(conn,cur)
    #查询数据库
    def select_infor(self,sqlStr,params):
        conn,cur = self.get_conn()
        try:
            cur.execute(sqlStr,params)
            datas = cur.fetchall()
            if len(datas) < 0:
                return []
            fields = [field[0] for field in cur.description]
            res = [dict(zip(fields,data)) for data in datas]
            return res
        except BaseException as e:
            print('数据库查询错误')
            return []
        finally:
            self.close_conn(conn,cur)
    #更新数据库
    def update_infor(self,sqlStr,params):
        conn, cur = self.get_conn()
        try:
            affected_row = cur.execute(sqlStr,params)
            conn.commit()
            return affected_row
        except BaseException as e:
            print(f'数据库更新错误{e}')
            return 0
        finally:
            self.close_conn(conn, cur)
    #读取配置文件
    def cofig(self):
        # 读取信息
        self.config = configparser.ConfigParser()
        try:
            self.config.read('config.ini')
            self.MYSQL_HOST = self.config.get('MYSQL', 'host')
            self.MYSQL_PORT = self.config.getint('MYSQL', 'port')
            self.MYSQL_USER = self.config.get('MYSQL', 'username')
            self.MYSQL_PASSWORD = self.config.get('MYSQL', 'password')
            self.MYSQL_DB = self.config.get('MYSQL', 'database')
        except BaseException as e:
            print(f'读取配置文件错误：{e}')